package com.ybb.dao;

import com.mysql.jdbc.StringUtils;
import com.ybb.pojo.User;
import com.ybb.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public int updatePassword(Long id, String oldPassword, String newPassword) {
        Connection connection = JdbcUtil.getConnection();
        String sql = "update smbms_user set userPassword=? where id=? ";
        PreparedStatement preparedStatement = null;
        int effectRows = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            Object[] params = {newPassword, id};


            effectRows = JdbcUtil.update(preparedStatement, params);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(null, preparedStatement, connection);
        }
        return effectRows;
    }

    @Override
    public List<User> getUsers(String queryName, long queryUserRole, int currentPageNum, int userCountPerPage) {
        Connection connection = JdbcUtil.getConnection();
        String sql = "select smbms_user.*,roleName\n" +
                "from smbms_user join smbms_role\n" +
                "on smbms_user.userRole=smbms_role.id\n" +
                "where 1=1 ";

        List<Object> params = new ArrayList<Object>();
        if (!StringUtils.isNullOrEmpty(queryName)) {
            sql += "and userName=? ";
            params.add(queryName);
        }


        if (queryUserRole > 0) {
            sql += "and userRole=? ";
            params.add(queryUserRole);
        }

        sql += "limit ?,? ";
        params.add((currentPageNum - 1) * userCountPerPage);
        params.add(userCountPerPage);

        List<User> userList = new ArrayList<User>();
        if (connection != null) {
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                rs = JdbcUtil.query(preparedStatement, params.toArray());

                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getLong("id"));
                    user.setUserCode(rs.getString("userCode"));
                    user.setUserName(rs.getString("userName"));
                    user.setGender(rs.getInt("gender"));
                    user.setAge(rs.getDate("birthday"));
                    user.setPhone(rs.getString("phone"));
                    user.setUserRole(rs.getLong("userRole"));
                    user.setUserRoleName(rs.getString("roleName"));
                    userList.add(user);

                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(rs, preparedStatement, connection);
            }
        }

        return userList;
    }

    public int getUsersCount(String queryName, long queryUserRole) {
        Connection connection = JdbcUtil.getConnection();
        int count = 0;
        if (connection != null) {
            String sql = "select count(*) from smbms_user where 1=1 ";

            List<Object> params = new ArrayList<Object>();
            if (!StringUtils.isNullOrEmpty(queryName)) {
                sql += "and userName=? ";
                params.add(queryName);
            }
            if (queryUserRole > 0) {
                sql += "and userRole=? ";
                params.add(queryUserRole);

            }
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                rs = JdbcUtil.query(preparedStatement, params.toArray());
                if (rs.next()) {
                    count = rs.getInt(1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(rs, preparedStatement, connection);
            }

        }
        return count;
    }

    @Override
    public User getUserById(long uid) {
        Connection connection = JdbcUtil.getConnection();
        User user = null;
        if (connection != null) {
            String sql = "select smbms_user.* ,roleName\n " +
                    "from smbms_user join smbms_role\n" +
                    "on smbms_user.userRole=smbms_role.id\n" +
                    "where smbms_user.id=?";

            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                rs = JdbcUtil.query(preparedStatement, new Object[]{uid});
                if (rs.next()) {
                    user = new User();
                    user.setId(uid);
                    user.setUserCode(rs.getString("userCode"));
                    user.setUserName(rs.getString("userName"));
                    user.setGender(rs.getInt("gender"));
                    user.setBirthday(rs.getDate("birthday"));
                    user.setPhone(rs.getString("phone"));
                    user.setAddress(rs.getString("address"));
                    user.setUserRole(rs.getLong("userRole"));
                    user.setUserRoleName(rs.getString("roleName"));

                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(rs, preparedStatement, connection);
            }
        }
        return user;

    }

    @Override
    public int upDateUser(User user) {
        Connection connection = JdbcUtil.getConnection();
        int updateRows = 0;
        if (connection != null) {
            String sql = "update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=? where id=?";
            List<Object> params = new ArrayList<Object>();
            params.add(user.getUserName());
            params.add(user.getGender());
            params.add(user.getBirthday());
            params.add(user.getPhone());
            params.add(user.getAddress());
            params.add(user.getUserRole());
            params.add(user.getId());
            PreparedStatement preparedStatement = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
                updateRows = JdbcUtil.update(preparedStatement, params.toArray());

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(null, preparedStatement, connection);
            }

        }
        return updateRows;
    }

    @Override
    public int deleteUser(long uid) {
        Connection connection = JdbcUtil.getConnection();
        int deleteRows = 0;
        if (connection != null) {
            String sql = "delete from smbms_user where id = ?";
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                deleteRows = JdbcUtil.update(preparedStatement, new Object[]{uid});
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(null, preparedStatement, connection);
            }
        }
        return deleteRows;
    }

    @Override
    public boolean isExists(long uid) {
        Connection connection = JdbcUtil.getConnection();

        if (connection != null) {
            String sql = "select * from smbms_user where id = ?";
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                resultSet = JdbcUtil.query(preparedStatement, new Object[]{uid});
                if (resultSet.next()) {
                    return true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(null, preparedStatement, connection);
            }
        }
        return false;
    }

    @Override
    public boolean hasUserCode(String userCode) {
        Connection connection = JdbcUtil.getConnection();
        if (connection != null) {
            String sql = "select * from smbms_user where userCode=?";
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                rs = JdbcUtil.query(preparedStatement, new Object[]{userCode});
                if (rs.next()) {
                    return true;
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(rs, preparedStatement, connection);
            }

        }
        return false;

    }

    @Override
    public int insertUser(User user) {
        Connection connection = JdbcUtil.getConnection();
        int insertRows=0;
        if (connection != null) {
            String sql = "insert into smbms_user(userCode,userName,userPassword,gender,birthday,phone,userRole,address,createdby,creationDate)" +
                    "values(?,?,?,?,?,?,?,?,?,?) ";
            ArrayList<Object> params = new ArrayList<Object>();
            params.add(user.getUserCode());
            params.add(user.getUserName());
            params.add(user.getUserPassword());
            params.add(user.getGender());
            params.add(user.getBirthday());
            params.add(user.getPhone());
            params.add(user.getUserRole());
            params.add(user.getAddress());
            params.add(user.getCreateBy());
            params.add(user.getCreationDate());

            PreparedStatement preparedStatement = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
                insertRows = JdbcUtil.update(preparedStatement,params.toArray());

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(null, preparedStatement, connection);
            }

        }
        return insertRows;
    }
}
